import mysql from 'mysql2/promise';

// 数据库配置
const dbConfig = {
  host: 'localhost',
  port: 3306,
  user: 'root',
  password: '245541zaqQ',
  database: 'newton_rollcall',
  charset: 'utf8mb4',
  timezone: '+08:00'
};

// 创建数据库连接
export async function createConnection() {
  try {
    const connection = await mysql.createConnection(dbConfig);
    return connection;
  } catch (error) {
    console.error('数据库连接错误:', error);
    throw error;
  }
}

// 创建连接池
export const pool = mysql.createPool({
  ...dbConfig,
  waitForConnections: true,
  connectionLimit: 10,
  queueLimit: 0
});

// 执行查询的辅助函数
export async function executeQuery(sql, params = []) {
  try {
    // 清理和验证参数
    const cleanParams = Array.isArray(params) ? params.filter(param => param !== undefined).map(param => {
      // 确保数值类型正确
      if (typeof param === 'string' && !isNaN(param) && param !== '') {
        return Number(param);
      }
      return param;
    }) : [];
    
    // 计算SQL中占位符数量
    const placeholderCount = (sql.match(/\?/g) || []).length;
    
    if (placeholderCount !== cleanParams.length) {
      console.error(`参数数量不匹配: SQL需要 ${placeholderCount} 个参数，但提供了 ${cleanParams.length} 个`);
      console.error('SQL:', sql);
      console.error('Params:', cleanParams);
      throw new Error(`参数数量不匹配: 需要 ${placeholderCount} 个，提供了 ${cleanParams.length} 个`);
    }
    
    const [rows] = await pool.execute(sql, cleanParams);
    return rows;
  } catch (error) {
    console.error('SQL执行错误:', error);
    throw error;
  }
}

// 获取单条记录
export async function getOne(sql, params = []) {
  const rows = await executeQuery(sql, params);
  return rows[0] || null;
}

// 事务处理
export async function transaction(callback) {
  const connection = await pool.getConnection();
  
  try {
    await connection.beginTransaction();
    const result = await callback(connection);
    await connection.commit();
    return result;
  } catch (error) {
    await connection.rollback();
    throw error;
  } finally {
    connection.release();
  }
}

export default pool;
